use RabbitsWare

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

drop PROCEDURE [dbo].[TweetApolloGetCompany]
go

CREATE PROCEDURE [dbo].[TweetApolloGetCompany]
	@companyName varchar(128)
as
begin
	select * 
	from [dbo].[TweetApolloCompany]
	where [Name]= @companyName
end
GO

drop PROCEDURE [dbo].[TweetApolloGetAvaiableCompanyOperator]
go

CREATE PROCEDURE [dbo].[TweetApolloGetAvaiableCompanyOperator]
	@companyId int
as
begin
	select * 
	from [TweetApolloCompanyOperator] CpOp
	where CpOp.[Company] = @companyId and CpOp.IsBusy = 0
end
GO

drop PROCEDURE [dbo].[TweetApolloMarkCompanyOperatorBusy]
go

CREATE PROCEDURE [dbo].[TweetApolloMarkCompanyOperatorBusy]
	@operatorId int
as
begin
	update [TweetApolloCompanyOperator]
	set IsBusy = 1
	where [OperatorId] = @operatorId
	
	declare @companyId int
	set @companyId = (select [Company] from [TweetApolloCompanyOperator] where [OperatorId] = @operatorId)

	if (select [Credits] from [TweetApolloCompany] where [CompanyId] = @companyId)  >= 0
	begin
		update [TweetApolloCompany]
		set [Credits] = [Credits]-1
		where [CompanyId] = @companyId 
	end
end
GO

drop PROCEDURE [dbo].[TweetApolloMarkCompanyOperatorAvaiable]
go

CREATE PROCEDURE [dbo].[TweetApolloMarkCompanyOperatorAvaiable]
	@operatorId int
as
begin
	update [TweetApolloCompanyOperator]
	set IsBusy = 0
	where [OperatorId] = @operatorId
end
GO

drop PROCEDURE [dbo].[TweetApolloGetCompanyOperator]
go

CREATE PROCEDURE [dbo].[TweetApolloGetCompanyOperator]
	@operatorName varchar(128)
as
begin
	select * 
	from [TweetApolloCompanyOperator] CpOp
	where CpOp.[Name] = @operatorName
end
GO


drop PROCEDURE [dbo].[TweetApolloGetLastProcessedMentionId]
go

CREATE PROCEDURE [dbo].[TweetApolloGetLastProcessedMentionId]
	@tweetApolloUserName varchar(128)
as
begin
	select top 1 [LastProcessedMentionId] 
	from [TweetApolloManager] tam
	where tam.[TweetApolloUserName] = @tweetApolloUserName
	order by tam.[LastProcessedMentionId] desc
end
GO

drop PROCEDURE [dbo].[TweetApolloSetLastProcessedMentionId]
go

CREATE PROCEDURE [dbo].[TweetApolloSetLastProcessedMentionId]
	@tweetApolloUserName varchar(128),
	@lastProcessedMentionId int
as
begin
	update [TweetApolloManager]
	set [LastProcessedMentionId] = @lastProcessedMentionId
	where [TweetApolloUserName] = @tweetApolloUserName
	
end
GO

drop PROCEDURE [dbo].[TweetApolloAddCompanyOperator]
go
CREATE PROCEDURE [dbo].[TweetApolloAddCompanyOperator]
	@companyId int,
	@companyOperatorName varchar(128)
as
begin

	declare @newCompanyOperatorId int
	set @newCompanyOperatorId = (select top 1 Company from [TweetApolloCompanyOperator] where Company = @companyId order by OperatorId desc)+1

	INSERT INTO [RabbitsWare].[dbo].[TweetApolloCompanyOperator]
           ([Company]
           ,[OperatorId]
           ,[Name]
           ,[IsBusy])
     VALUES
           (@companyId
           ,@newCompanyOperatorId
           ,@companyOperatorName
           ,0)
end
GO

drop PROCEDURE [dbo].[TweetApolloRemoveCompanyOperator]
go
CREATE PROCEDURE [dbo].[TweetApolloRemoveCompanyOperator]
	@companyId int,
	@companyOperatorId int
as
begin

	delete from [RabbitsWare].[dbo].[TweetApolloCompanyOperator] where Company = @companyId and OperatorId = @companyOperatorId

end
GO

